﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NewsAggregatorLibrary.DAL;

namespace NewsAggregatorLibrary
{
    public class StoriesMember : Stories
    {
        /// <summary>
        /// Retrieves all Stories from the DB.
        /// </summary>
        /// <returns></returns>
        public static StoriesMemberCollection RetrieveAll()
        {
            try
            {
                return DB.Select().From(Schema).ExecuteAsCustomCollection<Stories, StoriesCollection, StoriesMember, StoriesMemberCollection>();
            }
            catch (Exception e)
            {
                throw new Exception("An error has occured while generating a list of Stories " + e.Message, e);
            }
        }

        /// <summary>
        /// Retrieves all Stories from the DB.
        /// </summary>
        /// <returns></returns>
        public static StoriesMemberCollection RetrieveRelated(int storyID)
        {
            try
            {
                DataTable dt = StoreProcedureMember.GetRelatedStories(storyID);
                List<string> ids = dt.AsEnumerable().Select(x => x[0].ToString()).ToList();
                return RetrieveByIds(ids);
            }
            catch (Exception e)
            {
                throw new Exception("An error has occured while generating a list of Stories " + e.Message, e);
            }
        }

        /// <summary>
        /// Retrieves Stories matching an array of ids from the DB.
        /// </summary>
        /// <returns></returns>
        public static StoriesMemberCollection RetrieveByIds(List<string> storyIDs)
        {
            try
            {
                return DB.Select().From(Schema).Where(Columns.Id).In(storyIDs).ExecuteAsCustomCollection<Stories, StoriesCollection, StoriesMember, StoriesMemberCollection>();
            }
            catch (Exception e)
            {
                throw new Exception("An error has occured while generating a list of Stories " + e.Message, e);
            }
        }

        /// <summary>
        /// Retrieves all Stories from the DB by feedID.
        /// </summary>
        /// <param name="feedID">The feed ID.</param>
        /// <returns></returns>
        /// <exception cref="System.Exception">An error has occured while generating a list of Stories  + e.Message</exception>
        public static StoriesMemberCollection RetrieveByFeedID(int feedID)
        {
            try
            {
                return DB.Select().From(Schema).Where(Columns.RssID).IsEqualTo(feedID).ExecuteAsCustomCollection<Stories, StoriesCollection, StoriesMember, StoriesMemberCollection>();
            }
            catch (Exception e)
            {
                throw new Exception("An error has occured while generating a list of Stories " + e.Message, e);
            }
        }

        /// <summary>
        /// Retrieves all Stories from the DB by CategoryID.
        /// </summary>
        /// <param name="categoryID">The category ID.</param>
        /// <returns></returns>
        /// <exception cref="System.Exception">An error has occured while generating a list of Stories  + e.Message</exception>
        public static StoriesMemberCollection RetrieveByCategoryID(int categoryID)
        {
            try
            {
                return DB.Select().From(Schema).Where(Columns.CategoryID).IsEqualTo(categoryID).ExecuteAsCustomCollection<Stories, StoriesCollection, StoriesMember, StoriesMemberCollection>();
            }
            catch (Exception e)
            {
                throw new Exception("An error has occured while generating a list of Stories " + e.Message, e);
            }
        }

        /// <summary>
        /// Retrieves a StoriesMember by ID.
        /// </summary>
        /// <returns></returns>
        public static StoriesMember RetrieveByID(int id)
        {
            try
            {
                return DB.Select().From(Schema).Where(Columns.Id).IsEqualTo(id).ExecuteSingle<StoriesMember>();
            }
            catch (Exception e)
            {
                throw new Exception("An error has occured while retrieving StoriesMember with id=" +id+ e.Message, e);
            }
        }

        /// <summary>
        /// Adds a new StoriesMember to the DB.
        /// </summary>
        /// <param name="rssId">The RSS id.</param>
        /// <param name="title">The title.</param>
        /// <param name="link">The link.</param>
        /// <param name="description">The description.</param>
        /// <param name="pubDate">The pub date.</param>
        /// <param name="categoryID">The category ID.</param>
        /// <returns>
        /// new story ID
        /// </returns>
        /// <exception cref="System.Exception">An error has occured while inserting a new story.  + e.Message</exception>
        public static int AddStory(int rssId, string title, string link, string description, DateTime pubDate, int categoryID)
        {
            StoriesMember story = new StoriesMember();
            try
            {
                story.RssID = rssId;
                story.Title = title;
                story.Link = link;
                story.Description = description;
                story.PubDate = pubDate;
                story.Hits = 0;
                story.CategoryID = categoryID;
                story.Save();
            }
            catch (Exception e)
            {
                throw new Exception("An error has occured while inserting a new story. " + e.Message);

            }
            return story.Id;
        }

        /// <summary>
        /// Updates a story.
        /// </summary>
        /// <param name="id">The id of the feed to update.</param>
        /// <param name="rssId">The RSS id.</param>
        /// <param name="title">The title.</param>
        /// <param name="link">The link.</param>
        /// <param name="description">The description.</param>
        /// <param name="pubDate">The pub date.</param>
        /// <param name="hits">The hits.</param>
        /// <returns></returns>
        /// <exception cref="System.Exception"></exception>
        public static Boolean UpdateStory(int id, int rssId, string title, string link, string description, DateTime pubDate, int hits)
        {
            Boolean ifUpdate = false;
            try
            {
                StoriesMember story = RetrieveByID(id);

                if (story != null)
                {
                    DB.Update<Stories>()
                    .Set(Columns.RssID).EqualTo(rssId)
                    .Set(Columns.Title).EqualTo(title)
                    .Set(Columns.Link).EqualTo(link)
                    .Set(Columns.Description).EqualTo(description)
                    .Set(Columns.PubDate).EqualTo(pubDate)
                    .Set(Columns.Hits).EqualTo(hits)
                    .Where(Columns.Id).IsEqualTo(id).Execute();

                    ifUpdate = true;
                }
            }
            catch (Exception e)
            {
                throw new Exception("An error has occured while updating a StoriesMember with id=" + id + ". " + e.Message);
            }
            return ifUpdate;
        }

        /// <summary>
        /// Incriments Hits for the StoriesMember with the specified id.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns>The new number of hits</returns>
        public static int Hit(int id)
        {
            try
            {
                int hits = GetHits(id) + 1;
                DB.Update<Stories>()
                       .Set(Columns.Hits).EqualTo(hits)
                       .Where(Columns.Id).IsEqualTo(id).Execute();
                return hits;
            }
            catch (Exception e)
            {
                throw new Exception("An error while retrieving hits count for StoriesMember with id=" + id + ". " + e.Message);
            }
        }



        /// <summary>
        /// Gets the hits for the StoriesMember with the specified id.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns>The hits count</returns>
        /// <exception cref="System.Exception"></exception>
        public static int GetHits(int id)
        {
            try
            {
                return DB.Select(Columns.Hits).From(Schema).Where(Columns.Id).IsEqualTo(id).ExecuteScalar<int>();
            }
            catch (Exception e)
            {
                throw new Exception("An error while retrieving hits count for StoriesMember with id=" + id + ". " + e.Message);
            }
        }

        /// <summary>
        /// Retrieves the top10 all time.
        /// </summary>
        /// <returns></returns>
        public static StoriesMemberCollection RetrieveTop10()
        {
           return RetrieveTop10(null, null);
        }

        /// <summary>
        /// Retrieves the top10 all time.
        /// </summary>
        /// <returns></returns>
        public static StoriesMemberCollection RetrieveByKeywordSearch(string keyword)
        {
            RelationMemberCollection storyIDs = RelationMember.RetrieveByKeyword(keyword);
            List<int> list = storyIDs.AsEnumerable().Select(o => o.StroyID).ToList();
            return RetrieveByStoryIds(list);
        }

        public static StoriesMemberCollection RetrieveByStoryIds(List<int> list)
        {
            try
            {
                return DB.Select().From(Schema).Where(Columns.Id).In(list).ExecuteAsCustomCollection<Stories, StoriesCollection, StoriesMember, StoriesMemberCollection>();
            }
            catch (Exception e)
            {
                throw new Exception("An error has occured while retrieving StoriesMember"+ e.Message, e);
            }
        }

        /// <summary>
        /// Retrieves the top10 by date range.
        /// </summary>
        /// <param name="sDate">The s date.</param>
        /// <param name="eDate">The e date.</param>
        /// <returns></returns>
        public static StoriesMemberCollection RetrieveTop10(DateTime? sDate, DateTime? eDate)
        {
          return RetrieveTop(sDate, eDate, 10);
        }

        /// <summary>
        /// Retrieves the top X.
        /// </summary>
        /// <param name="sDate">The s date.</param>
        /// <param name="eDate">The e date.</param>
        /// <param name="numTop">The num top records to return.</param>
        /// <returns></returns>
        /// <exception cref="System.Exception"></exception>
        public static StoriesMemberCollection RetrieveTop(DateTime? sDate, DateTime? eDate, int numTop)
        {
            try
            {
                SubSonic.SqlQuery myQ = DB.Select().Top(numTop.ToString()).From(Schema);
                if (sDate != null)
                {
                    myQ = myQ.And(Columns.PubDate).IsGreaterThan(sDate);
                }
                if (eDate != null)
                {
                    myQ = myQ.And(Columns.PubDate).IsLessThan(eDate);
                }
                return myQ.OrderDesc(Columns.Hits).ExecuteAsCustomCollection<Stories, StoriesCollection, StoriesMember, StoriesMemberCollection>();
            }
            catch (Exception e)
            {
                throw new Exception("An error while retrieving top 10 stories" + e.Message);
            }
        }
    }

    public class StoriesMemberCollection : StoriesCollection
    {
    }
}
